# -*-coding:utf8-*-
import pymysql
from HnuclassSystem import config

class Database:
    def __init__(self, host, account, password, database_name, port) -> None:
        self.host = host
        self.account = account
        self.password = password
        self.database = database_name
        self.port = port
    

    def insertClass(self, class_) -> bool:
        '''
        插入课程
        '''
        values = (class_['name'], class_['teacher'], class_['room'], class_['week'], class_['position'][0], class_['position'][1], class_['stuid'], class_['qq'])
        sql = '''
            insert into hnuclass(name, teacher, room, week, day, section, stuid, qq)
            values(%s, %s, %s, %s, %s, %s, %s, %s)
        '''
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        status = False
        try:
            # 执行sql命令
            cursor.execute(sql, values)
            db.commit()
            status = True
        except:
            print('插入课程失败')
            db.rollback()
        finally:
            db.close()
        return status
    

    def selectClass(self, day, section, week) -> list:
        '''
        给定日期和节次，查询所有课程
        '''
        res = []
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        sql = '''
                select name, week, room, teacher, day, section, qq
                from hnuclass
                where day = %s
                and section = %s;
                '''
        try:
            cursor.execute(sql, (day, section))
            results = cursor.fetchall()
            if results:# 不为空时
                for r in results:
                    r_week = r[1]
                    r_week = r_week.split(',')
                    if week in r_week:
                        # 如果当前周次在此课程的持续周次中
                        res.append(r)
        except :
            print('数据查询失败')
        finally:
            db.close()
    
        return res  


    def selectOneDayClass(self, day, qq, week):
        '''
        查询某一日的课程
        '''

        res = []
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        sql = '''
                select name, week, room, teacher, day, section, qq
                from hnuclass
                where day = %s
                and qq = %s;
                '''
        try:
            cursor.execute(sql, (day, qq))
            results = cursor.fetchall()
            if results:# 不为空时
                for r in results:
                    r_week = r[1]
                    r_week = r_week.split(',')
                    if week in r_week:
                        # 如果当前周次在此课程的持续周次中
                        res.append(r)
        except :
            print('数据查询失败')
        finally:
            db.close()

        return res  


    def selectQQ(self):
        '''
        查询所有QQ
        '''
        res = []
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        sql = '''
                select qq_num
                from login;
                '''
        try:
            cursor.execute(sql)
            results = cursor.fetchall() # fetchall返回元组，每个子元素是查询到的一行数据，也是元组
            for r in results:
                res.append(r[0])
        except :
            print('数据查询失败')
        finally:
            db.close()
    
        return res  


    def insertAccount(self, stuid, qq):
        '''
        先添加账号
        '''
        # 创建连接
        db = pymysql.connect(host = self.host, user = self.account, password = self.password, database = self.database, port = self.port)
        cursor = db.cursor()
        status = False
        values = (stuid, qq)
        sql = '''
            select *
            from login
            where stuid = %s
            and qq_num = %s
        '''
        try:
            cursor.execute(sql, values)
            results = cursor.fetchall() # fetchall返回元组，每个子元素是查询到的一行数据，也是元组
            if not results:
                # 没有结果证明是第一次添加
                try:
                    # 执行sql命令
                    sql = '''
                            insert into login(stuid, qq_num)
                            values(%s, %s)
                        '''
                    cursor.execute(sql, values)
                    db.commit()
                    status = True
                except:
                    print('插入账号失败')
                    db.rollback()
        except :
            print('数据查询失败')
        finally:
            db.close()
        
        
        
        return status


database = Database(config.DATABASE_ADDRESS, config.DATABASE_ACCOUNT, config.DATABASE_PASSWORD, config.DATABASE_NAME, config.DATABASE_PORT)